﻿using System;
using System.Data;
using MySql.Data.MySqlClient;
using System.Text.RegularExpressions;
namespace Database
{
    public class MySQLConnection : ConnectionDB
    {
        #region Field
        private static MySQLConnection cnn = null;
        private string _strConnect = "";
        private MySqlConnection _connecttion = new MySqlConnection();
        #endregion

        #region Property
        /// <summary>
        /// Lấy và gán chuỗi kết nối.
        /// </summary>
        /// <value>
        /// Chuỗi kết nối.
        /// </value>
        public string Text
        {
            private get { return _strConnect; }
            set { _strConnect = value; }
        }
        #endregion

        #region Đóng mở kết nối
        private void Open()
        {
            _connecttion.ConnectionString = _strConnect;
            _connecttion.Open();
        }

        private void Close()
        {
            _connecttion.Close();
        }
        #endregion

        #region Hàm tạo đối tượng
        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        /// <returns></returns>
        public static MySQLConnection Create()
        {
            if (cnn == null)
            {
                cnn = new MySQLConnection();
                return cnn;
            }
            return cnn;
        }

        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="database">Tên database.</param>
        /// <returns></returns>
        public static MySQLConnection Create(string server, string database)
        {
            if (cnn == null)
            {
                cnn = new MySQLConnection(server, database);
                return cnn;
            }
            return cnn;
        }

        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="port">Cổng kết nối</param>
        /// <param name="database">Tên database.</param>
        /// <returns></returns>
        public static MySQLConnection Create(string server, string port, string database)
        {
            if (cnn == null)
            {
                cnn = new MySQLConnection(server, port, database);
                return cnn;
            }
            return cnn;
        }

        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="database">Tên database.</param>
        /// <param name="user">Tên đăng nhập.</param>
        /// <param name="password">Mật khẩu.</param>
        /// <returns></returns>
        public static MySQLConnection Create(string server, string database, string user, string password)
        {
            if (cnn == null)
            {
                cnn = new MySQLConnection(server, database, user, password);
                return cnn;
            }
            return cnn;
        }

        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="port">Cổng kết nối</param>
        /// <param name="database">Tên database.</param>
        /// <param name="user">Tên đăng nhập.</param>
        /// <param name="password">Mật khẩu.</param>
        /// <returns></returns>
        public static MySQLConnection Create(string server, string port, string database, string user, string password)
        {
            if (cnn == null)
            {
                cnn = new MySQLConnection(server, port, database, user, password);
                return cnn;
            }
            return cnn;
        }
        #endregion

        #region Khởi tạo và hủy
        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        private MySQLConnection()
        {
            _strConnect = "";
        }

        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="database">Tên database.</param>
        private MySQLConnection(string server, string database)
        {
            _strConnect = "server=" + server
                + "Port=3360"
                + ";database=" + database
                + ";uid=admin;pwd=";
        }

        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="port">Cổng kết nối</param>
        /// <param name="database">Tên database.</param>
        private MySQLConnection(string server, string port, string database)
        {
            _strConnect = "server=" + server
                + "Port=" + port
                + ";database=" + database
                + ";uid=admin;pwd=";
        }

        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="database">Tên database.</param>
        /// <param name="user">Tên đăng nhập.</param>
        /// <param name="password">Mật khẩu.</param>
        private MySQLConnection(string server, string database, string user, string password)
        {
            _strConnect = "server=" + server
                + "Port=3360"
                + ";database=" + database
                + "uid=" + user
                + ";pwd=" + password;
        }

        /// <summary>
        /// Tạo mới 1 đối tượng <see cref="MSSqlConnection" />.
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="port">Cổng kết nối</param>
        /// <param name="database">Tên database.</param>
        /// <param name="user">Tên đăng nhập.</param>
        /// <param name="password">Mật khẩu.</param>
        private MySQLConnection(string server, string port, string database, string user, string password)
        {
            _strConnect = "server=" + server
                + "Port=" + port
                + ";database=" + database
                + "uid=" + user
                + ";pwd=" + password;
        }


        /// <summary>
        /// Tạo chuỗi kết nối cho đối tượng
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="database">Tên database.</param>
        public void ConnectionText(string server, string database)
        {
            _strConnect = "server=" + server
                + "Port=3360"
                + ";database=" + database
                + ";uid=admin;pwd=";
        }

        /// <summary>
        /// Tạo chuỗi kết nối cho đối tượng
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="port">Cổng</param>
        /// <param name="database">Tên database.</param>
        public void ConnectionText(string server, string port, string database)
        {
            _strConnect = "server=" + server
                + "Port=" + port
                + ";database=" + database
                + ";uid=admin;pwd=";
        }

        /// <summary>
        /// Tạo chuỗi kết nối cho đối tượng
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="database">Tên database.</param>
        /// <param name="user">Tên đăng nhập.</param>
        /// <param name="password">Mật khẩu.</param>
        public void ConnectionText(string server, string database, string user, string password)
        {
            _strConnect = "server=" + server
                + "Port=3360"
                + ";database=" + database
                + "uid=" + user
                + ";pwd=" + password;
        }

        /// <summary>
        /// Tạo chuỗi kết nối cho đối tượng
        /// </summary>
        /// <param name="server">Tên server.</param>
        /// <param name="port">Cổng</param>
        /// <param name="database">Tên database.</param>
        /// <param name="user">Tên đăng nhập.</param>
        /// <param name="password">Mật khẩu.</param>
        public void ConnectionText(string server, string port, string database, string user, string password)
        {
            _strConnect = "server=" + server
                + "Port=" + port
                + ";database=" + database
                + "uid=" + user
                + ";pwd=" + password;
        }

        
        ~MySQLConnection()
        {
            if (Check())
            {
                _connecttion.Dispose();
            }
        }
        #endregion

        #region Kiểm tra kết nối
        /// <summary>
        /// Kiểm tra kết nối.
        /// </summary>
        /// <returns></returns>
        public override bool Check()
        {
            try
            {
                Open();
            }
            catch /*(Exception ex)*/
            {
                return false;
            }
            Close();
            return true;
        }
        #endregion

        #region Thực hiện lệnh thực thi SQL
        /// <summary>
        /// Thực thi lệnh sql
        /// </summary>
        /// <param name="execute">Chuỗi thực thi.</param>
        /// <returns>Số trường được thực thi</returns>
        public override int Execute(string execute)
        {
            int number;
            if (Check())
            {
                Open();
                MySqlCommand _command = new MySqlCommand();
                _command.Connection = _connecttion;
                _command.CommandText = execute;
                try
                {
                    number = _command.ExecuteNonQuery();
                }
                catch /*(Exception ex)*/
                {
                    Close();
                    return -1;
                }
                Close();
                return number;
            }
            else
            {
                return -2;
            }
        }

        /// <summary>
        /// Thực thi lệnh sql
        /// </summary>
        /// <param name="execute">
        /// Chuỗi thực thi với tham số là số nguyên dương nằm trong dấu {}.
        /// VD: Delete Table where id like {0};
        /// </param>
        /// <param name="obj">Các tham số.</param>
        /// <returns></returns>
        public override int Execute(string execute, params object[] obj)
        {
            #region Xử lý chuỗi execute
            string pattern = "{\\d+}";
            string result = null;
            int numberValue = 0;
            Regex Reg = new Regex(pattern);
            MatchCollection mc = Reg.Matches(execute);
            if (mc.Count > 0)
            {
                numberValue = mc.Count;
                string[] temp = Reg.Split(execute);
                for (int i = 0; i < temp.Length - 1; i++)
                {
                    result += temp[i] + "@value" + mc[i].Value.Substring(1, mc[i].Value.IndexOf('}') - 1);
                }
                result += temp[temp.Length - 1];
            }
            #endregion
            int number;
            if (Check())
            {
                Open();
                MySqlCommand _command = new MySqlCommand();
                _command.Connection = _connecttion;
                if (string.IsNullOrEmpty(result))
                {
                    _command.CommandText = execute;
                }
                else
                {
                    _command.CommandText = result;
                    for (int i = 0; i < numberValue; i++)
                    {
                        _command.Parameters.AddWithValue("@value" + i.ToString(), obj[i]);
                    }
                }
                try
                {
                    number = _command.ExecuteNonQuery();
                }
                catch /*(Exception ex)*/
                {
                    Close();
                    return -1;
                }
                Close();
                return number;
            }
            else
            {
                return -2;
            }
        }
        #endregion

        #region Thực hiện lệnh Query
        /// <summary>
        /// Thực thi câu lệnh truy vấn
        /// </summary>
        /// <param name="select">Chuỗi truy vấn.</param>
        /// <returns></returns>
        public override object[][] Querys(string select)
        {
            object[][] obj;
            if (Check())
            {
                DataSet _dataset = new DataSet();
                MySqlCommand _command = new MySqlCommand(select, _connecttion);
                MySqlDataAdapter _dataAdapter = new MySqlDataAdapter(_command);
                try
                {
                    _dataAdapter.Fill(_dataset, "Data_DataSet");
                    DataTable _datatable = _dataset.Tables["Data_DataSet"];
                    obj = new object[_datatable.Rows.Count][];
                    for (int i = 0; i < _datatable.Rows.Count; i++)
                    {
                        obj[i] = new object[_datatable.Columns.Count];
                        obj[i] = _datatable.Rows[i].ItemArray;
                    }
                }
                catch /*(Exception ex)*/
                {
                    return null;
                }
                return obj;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Thực thi câu lệnh truy vấn
        /// </summary>
        /// <param name="select">Chuỗi truy vấn.</param>
        /// <param name="obj">Các tham só.</param>
        /// <returns></returns>
        public override object[][] Querys(string select, params object[] obj)
        {
            #region Xử lý chuỗi select
            string pattern = "{\\d+}";
            string result = null;
            int numberValue = 0;
            Regex Reg = new Regex(pattern);
            MatchCollection mc = Reg.Matches(select);
            if (mc.Count > 0)
            {
                numberValue = mc.Count;
                string[] temp = Reg.Split(select);
                for (int i = 0; i < temp.Length - 1; i++)
                {
                    result += temp[i] + "@value" + mc[i].Value.Substring(1, mc[i].Value.IndexOf('}') - 1);
                }
                result += temp[temp.Length - 1];
            }
            #endregion
            object[][] objtemp;
            if (Check())
            {
                DataSet _dataset = new DataSet();
                MySqlCommand _command = new MySqlCommand();
                _command.Connection = _connecttion;
                if (string.IsNullOrEmpty(result))
                {
                    _command.CommandText = select;
                }
                else
                {
                    _command.CommandText = result;
                    for (int i = 0; i < numberValue; i++)
                    {
                        _command.Parameters.AddWithValue("@value" + i.ToString(), obj[i]);
                    }
                }
                MySqlDataAdapter _dataAdapter = new MySqlDataAdapter(_command);
                try
                {
                    _dataAdapter.Fill(_dataset, "Data_DataSet");
                    DataTable _datatable = _dataset.Tables["Data_DataSet"];
                    objtemp = new object[_datatable.Rows.Count][];
                    for (int i = 0; i < _datatable.Rows.Count; i++)
                    {
                        objtemp[i] = new object[_datatable.Columns.Count];
                        objtemp[i] = _datatable.Rows[i].ItemArray;
                    }
                }
                catch /*(Exception ex)*/
                {
                    return null;
                }
                return objtemp;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// Thực thi câu lệnh truy vấn
        /// </summary>
        /// <param name="select">Chuỗi truy vấn.</param>
        /// <returns></returns>
        public override DataTable Query(string select)
        {
            DataTable _datatable = new DataTable();
            if (Check())
            {
                DataSet _dataset = new DataSet();
                MySqlCommand _command = new MySqlCommand(select, _connecttion);
                MySqlDataAdapter _dataAdapter = new MySqlDataAdapter(_command);
                try
                {
                    _dataAdapter.Fill(_dataset, "Data_DataSet");
                    _datatable = _dataset.Tables["Data_DataSet"];
                }
                catch /*(Exception ex)*/
                {
                    return null;
                }
            }
            return _datatable;
        }

        /// <summary>
        /// Thực thi câu lệnh truy vấn
        /// </summary>
        /// <param name="select">Chuỗi truy vấn.</param>
        /// <param name="obj">Các tham só.</param>
        /// <returns></returns>
        public override DataTable Query(string select, params object[] obj)
        {
            #region Xử lý chuỗi select
            string pattern = "{\\d+}";
            string result = null;
            int numberValue = 0;
            Regex Reg = new Regex(pattern);
            MatchCollection mc = Reg.Matches(select);
            if (mc.Count > 0)
            {
                numberValue = mc.Count;
                string[] temp = Reg.Split(select);
                for (int i = 0; i < temp.Length - 1; i++)
                {
                    result += temp[i] + "@value" + mc[i].Value.Substring(1, mc[i].Value.IndexOf('}') - 1);
                }
                result += temp[temp.Length - 1];
            }
            #endregion
            DataTable _datatable = new DataTable();
            if (Check())
            {
                DataSet _dataset = new DataSet();
                MySqlCommand _command = new MySqlCommand();
                _command.Connection = _connecttion;
                if (string.IsNullOrEmpty(result))
                {
                    _command.CommandText = select;
                }
                else
                {
                    _command.CommandText = result;
                    for (int i = 0; i < numberValue; i++)
                    {
                        _command.Parameters.AddWithValue("@value" + i.ToString(), obj[i]);
                    }
                }
                MySqlDataAdapter _dataAdapter = new MySqlDataAdapter(_command);
                try
                {
                    _dataAdapter.Fill(_dataset, "Data_DataSet");
                    _datatable = _dataset.Tables["Data_DataSet"];
                }
                catch /*(Exception ex)*/
                {
                    return null;
                }
            }
            else
            {
                return null;
            }
            return _datatable;
        }
        #endregion


        public override DataTable QueryProcedure(string ProcName)
        {
            throw new NotImplementedException();
        }

        public override DataTable QueryProcedure(string ProcName, params object[] obj)
        {
            throw new NotImplementedException();
        }

        public override int ExecuteProcedure(string ProcName, params object[] obj)
        {
            throw new NotImplementedException();
        }
    }
}
